package com.eascs.archive.dao;

import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.archive.entity.ArchiveExportEntity;
import com.eascs.archive.entity.ArchiveExportQueryVo;
import com.eascs.archive.entity.ArchiveLoanEntity;
import com.eascs.archive.entity.ArchiveRecordEntity;
import com.eascs.web.sso.UserHolder;

@Repository
public class ArchiveAdminDao extends BaseO2oDao{
	
	/**
	 * 归档列表分页
	 * @param pageSize
	 * @param pageIndex
	 * @return
	 */
	public PageResult<Object[]> queryListPage(ArchiveLoanEntity entity, int pageSize, int pageIndex){
		StringBuffer sb=new StringBuffer();
		sb.append(" SELECT l.loanapply_no,l.bill_code,CONCAT(l.ltd_code,'-',l.ltd_name) as ltd_name,l.operator_name,u.user_full_name");
		sb.append(" ,l.bank_loan_name,b.loan_date,a.create_time,a.archive_no,l.is_place_on_file ");
		sb.append(" FROM o2o_loanapply l LEFT JOIN o2o_loan_user u ON l.loanapply_no=u.loanapply_no and u.spouse_id IS NULL AND u.sponsor_type IS NULL");
		sb.append(" LEFT JOIN o2o_archive_record a ON l. loanapply_no=a.loanapply_no  AND a.form_type_id='43004'");
		sb.append(" LEFT JOIN o2o_bank_loan_data b ON l.bill_code=b.bill_code");
		//sb.append(" WHERE /**DATA_PERMISSION(l.LTD)**/ l.loan_id=u.loan_id");
		
		sb.append(" WHERE ");
		
		sb.append(" 1=1 and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(entity.getXdjlNo())) {
			sb.append(" or l.operator_no in ("+entity.getXdjlNo()+"))");
		}else {
			sb.append(" or /**DATA_PERMISSION(l.ltd)**/ 1=1 ) ");
		}
		
		sb.append(" and l.loan_id=u.loan_id");
		
		//sb.append(" AND l.is_place_on_file IN ('RUN','CLO')  ");
		//sb.append(" AND b.loan_date IS NOT NULL ");
		sb.append(" AND l.loan_flg = 'Y'");
		
		if (StringUtils.isNotBlank(entity.getBillCode())) {
			sb.append(" AND l.bill_code='"+entity.getBillCode()+"'");
		}
		if (StringUtils.isNotBlank(entity.getUserFullName())) {
			sb.append(" AND u.user_full_name='"+entity.getUserFullName()+"'");
		}
		
		if (StringUtils.isNotBlank(entity.getPlaceOnFile())) {
			sb.append(" AND l.is_place_on_file='"+entity.getPlaceOnFile()+"'");
		}
		
		if (StringUtils.isNotBlank(entity.getArchiveNo())) {
			sb.append(" AND a.archive_no='"+entity.getArchiveNo()+"'");
		}
		sb.append(" ORDER BY  b.loan_date ASC ");
		SqlPageQuery<Object[]> sqlPageQuery=new SqlPageQuery<Object[]>(Object[].class, pageSize, pageIndex,sb.toString());
		return this.findDataWithPage(sqlPageQuery);
	}
	
	/**
	 * 二次归档列表分页
	 * @param pageSize
	 * @param pageIndex
	 * @return
	 */
	public PageResult<Object[]> repeatGdList(ArchiveLoanEntity entity, int pageSize, int pageIndex){
		StringBuffer sb=new StringBuffer();
		sb.append(" SELECT l.loanapply_no,a.id,a.gd_code,l.bill_code,u.user_full_name,CONCAT(l.ltd_code,'-',l.ltd_name) AS ltd_name,l.operator_name,l.bank_loan_name,b.loan_date,a.archive_no,a.archive_type,a.wf_state ,a.create_time ");
		sb.append(" FROM o2o_archive_record_repeat a  ");
		sb.append(" LEFT JOIN o2o_loanapply l ON l. loanapply_no=a.loanapply_no");
		sb.append(" LEFT JOIN o2o_loan_user u ON l.loanapply_no=u.loanapply_no AND u.spouse_id IS NULL AND u.sponsor_type IS NULL");
		sb.append(" LEFT JOIN o2o_bank_loan_data b ON l.bill_code=b.bill_code");
		
		//sb.append(" WHERE /**DATA_PERMISSION(l.LTD)**/ l.loan_id=u.loan_id");
		
		sb.append(" WHERE ");
		
		sb.append(" 1=1 and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(entity.getXdjlNo())) {
			sb.append(" or l.operator_no in ("+entity.getXdjlNo()+"))");
		}else {
			sb.append(" or /**DATA_PERMISSION(l.ltd)**/ 1=1 ) ");
		}
		
		sb.append(" and l.loan_id=u.loan_id");
		
		
		sb.append(" AND l.loan_flg = 'Y' AND a.gd_state = 0");
		
		if (StringUtils.isNotBlank(entity.getGdCode())) {
			sb.append(" AND a.gd_code='"+entity.getGdCode()+"'");
		}
		if (StringUtils.isNotBlank(entity.getBillCode())) {
			sb.append(" AND l.bill_code='"+entity.getBillCode()+"'");
		}
		if (StringUtils.isNotBlank(entity.getUserFullName())) {
			sb.append(" AND u.user_full_name='"+entity.getUserFullName()+"'");
		}
		
		if (StringUtils.isNotBlank(entity.getPlaceOnFile())) {
			//sb.append(" AND l.is_place_on_file='"+entity.getPlaceOnFile()+"'");
			sb.append(" AND a.wf_state='"+entity.getPlaceOnFile()+"'");
		}
		
		if (StringUtils.isNotBlank(entity.getArchiveNo())) {
			sb.append(" AND a.archive_no='"+entity.getArchiveNo()+"'");
		}
		sb.append(" ORDER BY a.create_time DESC ");
		SqlPageQuery<Object[]> sqlPageQuery=new SqlPageQuery<Object[]>(Object[].class, pageSize, pageIndex,sb.toString());
		return this.findDataWithPage(sqlPageQuery);
	}
	
	/**
	 * 归档列表导出查询
	 * @param pageSize
	 * @param pageIndex
	 * @return
	 */
	public PageResult<Object[]> queryExportByPage(ArchiveLoanEntity entity){
		StringBuffer sb=new StringBuffer();
		sb.append(" SELECT bill_code,");
		sb.append(" CASE bank_loan_name WHEN 'BC' THEN '中国银行' WHEN 'CBC' THEN '中国建设银行-深圳' WHEN 'CCB' THEN '中信银行' WHEN 'HXBC' THEN '华夏银行' WHEN 'EA' THEN '怡亚通' WHEN 'WEBANK' THEN '微众银行' WHEN 'GACZ' THEN '国安村镇银行' WHEN 'CCBHN' THEN '中国建设银行-湖南' END ,");
		sb.append(" CASE loan_type WHEN 'BK' THEN '终端贷' WHEN 'EA' THEN '接力贷' WHEN 'FQ' THEN '分期贷' WHEN 'BP' THEN '品牌贷' END ,");
		sb.append(" ltd_name,provincial,loan_uname,user_idcard,ea_mount,DATE_FORMAT(payment_time,'%Y-%m-%d'),DATE_FORMAT(term_time,'%Y-%m-%d'),operator_name,");
		sb.append(" CASE archive_state WHEN 'NEW' THEN '未归档' WHEN 'RUN' THEN '归档中' WHEN 'CLO' THEN '已归档' END ,");
		sb.append(" wf_node_state,DATE_FORMAT(wf_node_time,'%Y-%m-%d'),");
		sb.append(" ID_CARD,LOAN_APPLY_TABLE,BANK_NO,LOAN_CONTRACT,BANK_FLOW,LOAN_NO,BANK_FLOW_DES,BUSINESS_LICENCE,SD_NO,CREDIT_AUTHORITY_BOOK,WARM,BUSINESS_LICENCE_DES,LOAN_CLEAN_PROVE,CREDIT_REPORT,MORTGAGE_CONTRACT,MATH_CREDIT_AUTHORITY_BOOK,MATH_ID_CARD,PLEDGE_CONTRACT,COOPERATION_AGREEMENT,CABINET_LOAN_NO,CABINET_LOAN_CONTRACT,RESPONSIBILITY_BOOK,");
		sb.append(" DC_ATTORN,DC_PAYMENT_NO,DC_EMS_NO,DC_REPAYMENT_DES,dc_cabinet_no,dc_enter_no,DATE_FORMAT(send_time,'%Y-%m-%d'),send_company,send_no,cabinet_no,enter_no");
		sb.append(" FROM o2o_archive_export where 1=1 ");
		if (StringUtils.isNotBlank(entity.getBillCode())) {
			sb.append(" AND bill_code='"+entity.getBillCode()+"'");
		}
		if (StringUtils.isNotBlank(entity.getUserFullName())) {
			sb.append(" AND loan_uname='"+entity.getUserFullName()+"'");
		}
		if (StringUtils.isNotBlank(entity.getPlaceOnFile())) {
			sb.append(" AND archive_state='"+entity.getPlaceOnFile()+"'");
		}
		if (StringUtils.isNotBlank(entity.getArchiveNo())) {
			sb.append(" AND enter_no='"+entity.getArchiveNo()+"'");
		}
		sb.append(" ORDER BY  payment_time ASC ");
		SqlPageQuery<Object[]> sqlPageQuery=new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(), entity.getPageIndex(),sb.toString());
		return this.findDataWithPage(sqlPageQuery);
	}
	
	
	
	public PageResult<ArchiveExportQueryVo> queryBuildExportByPage(int pageSize, int pageIndex){
		StringBuffer sb=new StringBuffer();
		sb.append(" SELECT l.loanapply_no,l.bill_code,l.ltd,l.ltd_code,l.ltd_name,l.loan_type,l.operator_no,l.operator_name,u.user_full_name,u.document_no,l.bank_loan_name,");
		sb.append(" b.loan_date,b.loan_amount,b.loan_term,a.create_time,a.archive_no,l.is_place_on_file ");
		sb.append(" FROM o2o_loanapply l LEFT JOIN o2o_loan_user u ON l.loanapply_no=u.loanapply_no AND u.spouse_id IS NULL AND u.sponsor_type IS NULL");
		sb.append(" LEFT JOIN o2o_archive_record a ON l. loanapply_no=a.loanapply_no  AND a.form_type_id='43004'");
		sb.append(" LEFT JOIN o2o_bank_loan_data b ON l.bill_code=b.bill_code");
		sb.append(" WHERE l.loan_id=u.loan_id");
		//sb.append(" AND b.loan_date IS NOT NULL ");
		sb.append(" AND l.loan_flg = 'Y'");
		sb.append(" ORDER BY  b.loan_date ASC ");
		SqlPageQuery<ArchiveExportQueryVo> sqlPageQuery=new SqlPageQuery<ArchiveExportQueryVo>(ArchiveExportQueryVo.class, pageSize, pageIndex,sb.toString());
		return this.findDataWithPage(sqlPageQuery);
	}
	
	public int insertArchiveExport(ArchiveExportEntity entity){
		if (entity == null) {
			return 0;
		}
		return o2oJdbcSession.insert(entity);
	}
	
	public int deleteArchiveExport(String loanapplyno){
		if (StringUtils.isBlank(loanapplyno)) {
			return 0;
		}
		String sql="DELETE  FROM o2o_archive_export WHERE loanapply_no =? ";
		return o2oJdbcSession.execute(sql, loanapplyno);
	}
	
	public int deleteAllArchiveExport(){
		String sql="DELETE FROM o2o_archive_export";
		return o2oJdbcSession.execute(sql);
	}
	
	public ArchiveExportEntity queryArchiveExport(String loanapplyno){
		if (StringUtils.isBlank(loanapplyno)) {
			return null;
		}
		String sql="select * FROM o2o_archive_export WHERE loanapply_no =? ";
		return o2oJdbcSession.findEntity(sql, ArchiveExportEntity.class, loanapplyno);
	}
	
}
